class CreateSql {
    constructor() {
        this.description = "SQL生成器@zsduan"
        this.version = '2.0.1';
    }
    #sqlParts = [];

    // 默认是SELECT操作
    #currentOperation = 'SELECT';

    // 定义SQL语句部分的标准顺序
    #sql_order = {
        SELECT: ['select', 'from', 'join', 'on', 'where', 'group', 'having', 'order', 'limit', 'offset'],
        UPDATE: ['update', 'set', 'where', 'order', 'limit'],
        INSERT: ['insert', 'value', 'values'],
        DELETE: ['delete', 'where', 'order', 'limit']
    };

    #setOperation(operation) { // 私有方法
        if (this.#sql_order[operation]) {
            this.#currentOperation = operation;
        } else {
            throw new Error(`Unsupported SQL operation: ${operation}`);
        }
        return this;
    }

    #addPart(type, sql) { // 私有方法
        if (sql && sql.trim()) {
            const index = this.#sqlParts.findIndex(part => part.type === type);
            if (index >= 0) {
                this.#sqlParts[index].sql += ` ${sql}`; // 更新已存在的部分
            } else {
                this.#sqlParts.push({ type, sql }); // 添加新的部分
            }
        }
        return this;
    }

    /**
     * @name select 
     * @description 构建SELECT语句
     * @param {string|array} query - 要选择的字段，可以是字符串或数组。
     * @param {boolean} isDistinct - 是否使用DISTINCT关键字。
     * @example createSql().select('*').from('table').build();
     * @example createSql().select(['name', 'age']).from('table').build();
     * */
    select(query = '*', isDistinct = false) {
        const distinctPrefix = isDistinct ? 'DISTINCT ' : '';
        let sql = `SELECT ${distinctPrefix}`;
        if (typeof query === 'string') {
            sql += query;
        } else if (Array.isArray(query)) {
            sql += query.join(', ');
        }
        return this.#setOperation('SELECT').#addPart('select', sql);
    }

    /**
     * @name from
     * @description 构建FROM语句
     * @param {string|array} table_name - 要选择的表名，可以是字符串或数组。
     * @example createSql().select().from('table').build();
     * @example createSql().select().from(['table1', 'table2']).build();
     * */
    from(table_name) {
        if (table_name && table_name.trim()) {
            if (typeof table_name === 'string') {
                return this.#addPart('from', `FROM ${table_name}`);
            } else if (Array.isArray(table_name)) {
                return this.#addPart('from', `FROM ${table_name.join(', ')}`);
            }
        }
        return this;
    }

    /**
     * @name join
     * @description 构建JOIN语句
     * @param {string} join_table_name - 要连接的表名。
     * @param {string} on_condition - 连接条件。
     * @example createSql().select().from('table').join('other_table').build();
     * @example createSql().select().from('table').join('other_table', 'table.id = other_table.id').build();
     * */
    join(join_table_name, on_condition) {
        if (join_table_name && join_table_name.trim()) {
            let sql = `INNER JOIN ${join_table_name}`;
            if (on_condition && on_condition.trim()) {
                sql += ` ON ${on_condition}`;
            }
            return this.#addPart('join', sql);
        }
        return this;
    }
    /**
     * @name leftJoin
     * @description 构建LEFT JOIN语句
     * @param {string} left_join_table_name - 要连接的表名。
     * @param {string} on_condition - 连接条件。
     * @example createSql().select().from('table').leftJoin('other_table').build();
     * @example createSql().select().from('table').leftJoin('other_table', 'table.id = other_table.id').build();
     * */
    leftJoin(left_join_table_name, on_condition) {
        if (left_join_table_name && left_join_table_name.trim()) {
            let sql = `LEFT JOIN ${left_join_table_name}`;
            if (on_condition && on_condition.trim()) {
                sql += ` ON ${on_condition}`;
            }
            return this.#addPart('join', sql); // 使用相同的类型标识符
        }
        return this;
    }

    /**
     * @name crossJoin
     * @description 构建CROSS JOIN语句
     * @param {string | Function} cross_join_condition - 要连接的表名。
     * @example createSql().select().from('table').crossJoin('other_table').build();
     * */
    crossJoin(cross_join_condition) {
        cross_join_condition = handleFun(cross_join_condition);
        if (cross_join_condition && cross_join_condition.trim()) {
            return this.#addPart('join', `CROSS JOIN ${cross_join_condition}`);
        }
        return this;
    }
    /**
     * @name on
     * @description 构建ON语句
     * @param {string | Function} on_condition - 连接条件。
     * @example createSql().select().from('table').join('other_table').on('table.id = other_table.id').build();
     * */
    on(on_condition, on_condition_two, on_condition_three) {
        on_condition = handleFun(on_condition);
        if (!on_condition.trim()) {
            return this;
        }
        if (Array.isArray(on_condition)) {
            return this.#addPart('on', ` ON ${on_condition.join(" AND ")} `);
        }
        if (on_condition_three) {
            return this.#addPart('on', ` ON ${on_condition}  ${on_condition_two}  ${on_condition_three} `);
        }
        if (on_condition_two) {
            return this.#addPart('on', ` ON ${on_condition} AND ${on_condition_two} `);
        }
        return this.#addPart('on', `ON ${on_condition}`);
    }
    /**
     * @name where
     * @description 构建WHERE语句
     * @param {string | Function | Object} where_condition - 条件或条件生成函数。
     * @param {string} where_condition_two - 第二个条件 / 操作符。
     * @param {string} where_condition_three - 第三个条件 / 只有两个条件时 操作符为 =。
     * @example createSql().select().from('table').where('id > 1').build();
     * @example createSql().select().from('table').where('id', '1').build();
     * @example createSql().select().from('table').where('id', '=', '1').build();
     * @example createSql().select().from('table').where(() => someCondition ? 'id > 1' : '').build();
     * @throws {Error} 如果 where_condition 是一个返回 Promise 的函数。
     * */
    where(where_condition, where_condition_two, where_condition_three) {
        where_condition = handleFun(where_condition)
        if(typeof where_condition === 'object'){
            where_condition = Object.keys(where_condition).map(key => {
                const value = where_condition[key];
                if (typeof value === 'string' && value.includes(' ')) {
                    return `${key} = '${value}'`;
                } else {
                    return `${key} = ${value}`;
                }
            }).join(' AND ');
        }

        if (where_condition && where_condition.trim()) {
            let sql = 'WHERE ';
            if (where_condition_three) {
                sql += `${where_condition} ${where_condition_two} ${where_condition_three}`;
            } else if (where_condition_two) {
                sql += `${where_condition} = ${where_condition_two}`;
            } else {
                sql += where_condition;
            }
            return this.#addPart('where', sql);
        }
        return this;
    }
    /**
     * @name group
     * @description 构建GROUP BY语句
     * @param {string | Function} groupBy - 分组字段。
     * @example createSql().select().from('table').group('id').build();
     * */
    group(groupBy) {
        groupBy = handleFun(groupBy)
        if (groupBy && groupBy.trim()) {
            return this.#addPart('group', `GROUP BY ${groupBy}`);
        }
        return this;
    }
    /**
     * @name having
     * @description 构建HAVING语句
     * @param {string | Function} having_condition - 条件。
     * @example createSql().select().from('table').group('id').having('COUNT(id) > 1').build();
     * */
    having(having_condition) {
        having_condition = handleFun(having_condition)
        if (having_condition && having_condition.trim()) {
            return this.#addPart('having', `HAVING ${having_condition}`);
        }
        return this;
    }
    /**
     * @name order
     * @description 构建ORDER BY语句
     * @param {string | Function} order_condition - 排序字段1。
     * @param {string} order_condition_two - 排序字段1。
     * */
    order(order_condition, order_condition_two) {
        order_condition = handleFun(order_condition)
        if (order_condition && order_condition.trim()) {
            let sql = `ORDER BY ${order_condition}`;
            if (order_condition_two && order_condition_two.trim()) {
                sql += ` ${order_condition_two}`;
            }
            return this.#addPart('order', sql);
        }
        return this;
    }
    /**
     * @name limit
     * @description 构建LIMIT语句
     * @param {string | Function} limit_condition - 条件。
     * @param {string} limit_condition_two - 第二个条件。
     * @example createSql().select().from('table').limit('1,10').build();
     * @example createSql().select().from('table').limit(1, 10).build();
     * */
    limit(limit_condition, limit_condition_two) {
        limit_condition = handleFun(limit_condition)
        limit_condition ? limit_condition = limit_condition.toString() : null;
        if (limit_condition && limit_condition.trim()) {
            let sql = `LIMIT ${limit_condition}`;
            limit_condition_two ? limit_condition_two = limit_condition_two.toString() : null;
            if (limit_condition_two && limit_condition_two.trim()) {
                sql += `, ${limit_condition_two}`;
            }
            return this.#addPart('limit', sql);
        }
        return this;
    }
    /**
     * @name offset
     * @description 构建OFFSET语句
     * @param {string | Function} offset_condition - 条件。
     * @param {string} offset_condition_two - 第二个条件。
     * @example createSql().select().from('table').offset('1,10').build();
     * @example createSql().select().from('table').offset(1, 10).build();
     * */
    offset(offset_condition, offset_condition_two) {
        offset_condition = handleFun(offset_condition)
        if (offset_condition && offset_condition.trim()) {
            let sql = `OFFSET ${offset_condition}`;
            if (offset_condition_two && offset_condition_two.trim()) {
                sql += `, ${offset_condition_two}`;
            }
            return this.#addPart('offset', sql);
        }
        return this;
    }
    /**
     * @name update
     * @description 构建UPDATE语句
     * @param {string} update_table_name - 要更新的表名。
     * @example createSql().update('table').set('id = 1').build();
     * */
    update(update_table_name) {
        if (update_table_name && update_table_name.trim()) {
            return this.#setOperation('UPDATE').#addPart('update', `UPDATE ${update_table_name}`);
        }
        return this;
    }
    /**
     * @name set
     * @description 构建SET语句
     * @param {string | object | Function} set_condition - 要更新的字段。
     * @example createSql().update('table').set('id = 1').build();
     * @example createSql().update('table').set({id : 1}).build();
     * */
    set(set_condition) {
        set_condition = handleFun(set_condition)
        if (!set_condition) {
            return this;
        }
        if (typeof set_condition === 'string') {
            return this.#addPart('set', `SET ${set_condition}`);
        } else if (typeof set_condition === 'object') {
            const setClauses = Object.entries(set_condition).map(([key, value]) =>
                `${key} = ${typeof value === 'string' ? `'${value}'` : value}`
            ).join(', ');
            return this.#addPart('set', `SET ${setClauses}`);
        }
    }
    /**
     * @name insert
     * @description 构建INSERT语句
     * @param {string} insert_table_name - 要更新的表名。
     * @example createSql().insert('table').value('id = 1').build();
     * */
    insert(insert_table_name) {
        if (insert_table_name && insert_table_name.trim()) {
            return this.#setOperation('INSERT').#addPart('insert', `INSERT INTO ${insert_table_name}`);
        }
        return this;
    }
    /**
     * @name value
     * @description 构建VALUES语句
     * @param {string | object | Function} value_condition - 要更新的字段。
     * @example createSql().insert('table').value('id = 1').build();
     * @example createSql().insert('table').value({id : 1}).build();
     * */
    value(value_condition) {
        value_condition = handleFun(value_condition)
        if (!value_condition) {
            return this;
        }
        if (typeof value_condition === 'string' && !value_condition.trim()) {
            return this.#addPart('value', `VALUES (${value_condition})`);
        } else if (typeof value_condition === 'object') {
            const keys = Object.keys(value_condition).join(', ');
            const values = Object.values(value_condition).map(val =>
                typeof val === 'string' ? `'${val}'` : val
            ).join(', ');
            return this.#addPart('value', `(${keys}) VALUES (${values})`);
        }
        return this;
    }
    /**
     * @name values
     * @description 构建VALUES语句
     * @param {string[] | Function} value_conditions - 要更新的字段。
     * @example createSql().insert('table').values([{id : 1}, {id : 2}]).build();
     * */
    values(value_conditions) {
        value_conditions = handleFun(value_conditions)
        if (Array.isArray(value_conditions) && value_conditions.length > 0) {
            const firstItem = value_conditions[0];
            const keys = Object.keys(firstItem).join(', ');
            const valuesList = value_conditions.map(item =>
                `(${Object.values(item).map(val =>
                    typeof val === 'string' ? `'${val}'` : val
                ).join(', ')})`
            ).join(', ');
            return this.#addPart('values', `(${keys}) VALUES ${valuesList}`);
        }
        return this;
    }
    /**
     * @name delete
     * @description 构建DELETE语句
     * @param {string} delete_table_name - 要更新的表名。
     * @example createSql().delete('table').where('id = 1').build();
     * */
    delete(delete_table_name) {
        if (delete_table_name && delete_table_name.trim()) {
            return this.#setOperation('DELETE').#addPart('delete', `DELETE FROM ${delete_table_name}`);
        }
        return this;
    }
    /**
     * @name in
     * @description 构建IN语句
     * @param {string | Function} in_condition - 条件。
     * @example createSql().select().from('table').in('id IN (1, 2, 3)').build();
     * */
    in(in_condition ) {
        in_condition = handleFun(in_condition)
        if (in_condition && in_condition.trim()) {
            return this.#addPart('in', `IN ${in_condition}`);
        }
        return this;
    }
    /**
     * @name notin
     * @description 构建NOT IN语句
     * @param {string | Function} notin_condition - 条件。
     * @example createSql().select().from('table').where('ID').notin('(1, 2, 3)').build();
     * 
     * */ 
    notin(notin_condition) {
        notin_condition = handleFun(notin_condition)
        if (notin_condition && notin_condition.trim()) {
            return this.#addPart('andwhere', ` NOT IN ${notin_condition}`);
        }
        return this;
    }
    /**
     * @name andwhere
     * @description 构建AND语句
     * @param {string | Function} and_condition - 条件。
     * @example createSql().select().from('table').where('id = 1').andwhere('name = zsduan').build();
     * */
    andwhere(and_condition) {
        and_condition = handleFun(and_condition)
        if (and_condition && and_condition.trim()) {
            return this.#addPart('where', `AND ${and_condition}`);
        }
        return this;
    }
    /**
     * @name and
     * @description 构建AND语句
     * @param {string | Function} and_condition - 条件。
     * @param {string} and_condition_two - 条件2 / 操作符 只有两个参数的时候 自动为 =。
     * @param {string} and_condition_three - 条件3。
     * @example createSql().select().from('table').where('id = 1').and('name = zsduan').build();
     * @example createSql().select().from('table').where('id = 1').and('name', 'zsduan').build();
     * @example createSql().select().from('table').where('id = 1').and('name', '=' , 'zsduan').build();
     * */
    and(and_condition , and_condition_two , and_condition_three) {
        and_condition = handleFun(and_condition)
        if(and_condition_three && and_condition_three.trim()){
            return this.#addPart('where', `AND ${and_condition} ${and_condition_two} ${and_condition_three}`);
        }
        if(and_condition_two && and_condition_two.trim()){
            return this.#addPart('where', `AND ${and_condition} = ${and_condition_two}`);
        }
        if (and_condition && and_condition.trim()) {
            return this.#addPart('where', `AND ${and_condition}`);
        }
        return this;
    }

    /**
     * @name or
     * @description 构建OR语句
     * @param {string | object | Function} or_condition - 条件。
     * @example createSql().select().from('table').where('id = 1').or('name = zsduan').build();
     * */
    or(or_condition) {
        or_condition = handleFun(or_condition)
        if(typeof or_condition === 'object'){
            for(let key in or_condition){
                this.#addPart('where', `OR ${key} ${or_condition[key]}`);
            }
            return this;
        }
        if (or_condition && or_condition.trim()) {
            return this.#addPart('where', `OR ${or_condition}`);
        }
        return this;
    }
    /**
     * @name custom
     * @description 自定义语句
     * @param {string} custom_condition - 条件。
     * @example createSql().select().from('table').where('id = 1').custom('or name = zsduan').build();
     * */
    custom(custom_condition) {
        if (custom_condition && custom_condition.trim()) {
            return this.#addPart('custom', custom_condition);
        }
        return this;
    }
    /**
     * @name customAll
     * @description 自定义所有语句
     * @param {string} custom_condition - 条件。
     * @example createSql().customAll('select * from table where name = zsduan').build();
     * */
    customAll(custom_condition) {
        if (custom_condition && custom_condition.trim()) {
            this.#sqlParts = [{ type: 'custom', sql: custom_condition }];
        } else {
            this.#sqlParts = [];
        }
        return this;
    }
    /**
     * @name end
     * @description 构建SQL语句 返回原本SQL语句
     * @example createSql().select().from('table').where('id = 1').end();
     * @returns {string} 返回原本SQL语句 按照你的SQL语句顺序返回
     * */
    end() {
        return this.#sqlParts.map(part => part.sql).join(' ').trim();
    }
    /**
     * @name build/构建
     * @description 构建SQL语句 返回构建完成的SQL语句
     * @example createSql().select().from('table').where('id = 1').build();
     * @returns {string} 构建完成的SQL语句 按照标准返回
     * */
    build() {
        const order = this.#sql_order[this.#currentOperation];
        const orderedParts = [];

        // 按照标准顺序排列 SQL 部分
        for (const type of order) {
            const index = this.#sqlParts.findIndex(part => part.type === type);
            if (index !== -1) {
                orderedParts.push(this.#sqlParts[index]);
            }
        }
        const otherParts = this.#sqlParts.filter(part => !order.includes(part.type));
        const allParts = [...orderedParts, ...otherParts];
        let sql = allParts.map(part => part.sql).join(' ').trim();
        if(sql.indexOf('WHERE') == -1 && sql.indexOf('AND') != -1){
            sql = sql.replace(/\sAND/, ' WHERE');
        }
        return sql;
    }
}


/**处理函数*/
function handleFun(operation) {
    let result ;
    if(typeof operation === 'function'){
        result = operation();
        if(result instanceof Promise){
            console.warn('传入的函数不能使用Promise函数')
        }
        if(result == undefined || result === null){
            console.warn('传入的函数没有返回值,请返回值,已经默认返回空字符串');
            result = ''
        }
    }else{
        result = operation;
    }
    return result ;
}

/**
 * 创建SQL语句
 * */ 
const createSql = () => {
    return new CreateSql()
}

module.exports = createSql;